import sys
import os
import logging
import re
import json
from typing import Dict, List, Any, Optional, Tuple

# 添加项目根路径到 sys.path
sys.path.append(os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', '..'))

# 导入 MySQLUtil
from shared.utils.MySQLUtil import MySQLUtil

# 设置日志
logger = logging.getLogger(__name__)

def get_各学历及总体_按省内就业和省外就业分布(
    project_id: int,
    questionnaire_ids: List[int],
    product_code: Optional[str] = None,
    project_code: Optional[str] = None,
    region_code: Optional[str] = None,
    education: Optional[str] = None
) -> Dict[str, Any]:
    """
    各学历及总体，按省内就业和省外就业分布 - 指标计算函数
    
    ## 指标说明
    该指标用于统计不同学历毕业生在省内和省外的就业分布情况，包括各学历层次的具体分布和总体分布。
    计算基于学生客观数据表，筛选就业省内外字段不为空的样本，按照省内外分布统计人数及占比。
    
    ## Args
        project_id (int): 项目ID，用于查询项目配置信息
        questionnaire_ids (List[int]): 问卷ID集合，用于确定数据范围
        product_code (Optional[str]): 产品编码，用于路由到特定计算逻辑
        project_code (Optional[str]): 项目编码，用于路由到特定计算逻辑
        region_code (Optional[str]): 区域编码，用于路由到特定计算逻辑
        education (Optional[str]): 学历筛选条件，可选值：本科毕业生/专科毕业生/硕士研究生/博士研究生
    
    ## 示例
    ### 输入
    ```json
    {
        "project_id": 5895,
        "questionnaire_ids": [11158, 11159]
    }
    ```
    
    ### 输出
    ```json
    {
        "success": true,
        "message": "ok", 
        "code": 0,
        "result": [
            {
                "group": "本科毕业生",
                "local": {"count": 500, "percentage": 0.65},
                "remote": {"count": 270, "percentage": 0.35}
            },
            {
                "group": "总体",
                "local": {"count": 1200, "percentage": 0.6},
                "remote": {"count": 800, "percentage": 0.4}
            }
        ]
    }
    ```
    """
    logger.info(f"开始计算指标: 各学历及总体，按省内就业和省外就业分布, 项目ID: {project_id}")
    
    try:
        db = MySQLUtil()  

        # 1. 查询项目配置信息
        project_sql = """
        SELECT client_code, item_year FROM client_item WHERE id = %s
        """
        project_info = db.fetchone(project_sql, (project_id,))
        if not project_info:
            raise ValueError(f"未找到项目ID={project_id}的配置信息")

        client_code = project_info['client_code']
        item_year = project_info['item_year']
        
        logger.info(f"项目配置: client_code={client_code}, item_year={item_year}")

        # 2. 计算 shard_tb_key
        shard_tb_key = re.sub(r'^[A-Za-z]*0*', '', client_code)
        logger.info(f"计算得到 shard_tb_key: {shard_tb_key}")

        # 3. 定义标准学历枚举
        education_levels = ['本科毕业生', '专科毕业生', '硕士研究生', '博士研究生']
        
        # 4. 构建结果集
        result = []
        
        # 5. 按学历分组计算
        for edu in education_levels:
            if education and edu != education:
                continue
                
            sql = f"""
            SELECT
                job_around,
                COUNT(*) AS count
            FROM
                dim_client_target_baseinfo_student_calc_{item_year}
            WHERE
                shard_tb_key = %s
                AND education = %s
                AND job_around != ''
            GROUP BY
                job_around
            """
            counts = db.fetchall(sql, (shard_tb_key, edu))
            
            if not counts:
                logger.warning(f"未找到学历为 {edu} 的数据")
                continue
            
            # 记录查询到的job_around值，便于调试
            job_around_values = [row['job_around'] for row in counts]
            logger.info(f"学历 {edu} 查询到的job_around值: {job_around_values}")
                
            total = sum([row['count'] for row in counts])
            # job_around 的值是中文："省内就业"、"省外就业"
            local = next((row for row in counts if row['job_around'] == '省内就业'), None)
            remote = next((row for row in counts if row['job_around'] == '省外就业'), None)
            
            local_count = local['count'] if local else 0
            remote_count = remote['count'] if remote else 0
            
            logger.info(f"学历 {edu}: 省内={local_count}, 省外={remote_count}, 总计={total}")
            
            result.append({
                "group": edu,
                "local": {
                    "count": local_count,
                    "percentage": round(local_count / total, 4) if total > 0 else 0
                },
                "remote": {
                    "count": remote_count,
                    "percentage": round(remote_count / total, 4) if total > 0 else 0
                }
            })
        
        # 6. 计算总体数据
        total_sql = f"""
        SELECT
            job_around,
            COUNT(*) AS count
        FROM
            dim_client_target_baseinfo_student_calc_{item_year}
        WHERE
            shard_tb_key = %s
            AND job_around != ''
        GROUP BY
            job_around
        """
        total_counts = db.fetchall(total_sql, (shard_tb_key,))
        
        if total_counts:
            # 记录总体数据的job_around值
            total_job_around_values = [row['job_around'] for row in total_counts]
            logger.info(f"总体查询到的job_around值: {total_job_around_values}")
            
            total_all = sum([row['count'] for row in total_counts])
            # job_around 的值是中文："省内就业"、"省外就业"
            local_all = next((row for row in total_counts if row['job_around'] == '省内就业'), None)
            remote_all = next((row for row in total_counts if row['job_around'] == '省外就业'), None)
            
            local_all_count = local_all['count'] if local_all else 0
            remote_all_count = remote_all['count'] if remote_all else 0
            
            logger.info(f"总体: 省内={local_all_count}, 省外={remote_all_count}, 总计={total_all}")
            
            result.append({
                "group": "总体",
                "local": {
                    "count": local_all_count,
                    "percentage": round(local_all_count / total_all, 4) if total_all > 0 else 0
                },
                "remote": {
                    "count": remote_all_count,
                    "percentage": round(remote_all_count / total_all, 4) if total_all > 0 else 0
                }
            })

        logger.info(f"指标 '各学历及总体，按省内就业和省外就业分布' 计算成功")
        return {
            "success": True,
            "message": "ok",
            "code": 0,
            "result": result
        }

    except Exception as e:
        logger.error(f"计算指标 '各学历及总体，按省内就业和省外就业分布' 时发生错误: {str(e)}", exc_info=True)
        return {
            "success": False,
            "message": f"数据获取失败: 各学历及总体，按省内就业和省外就业分布",
            "code": 500,
            "error": str(e)
        }